# -*- coding: utf-8 -*-

from odoo import models, fields, api
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
import psycopg2
import os
import sys
import logging
import calendar

db_name = "odoo"
db_user = "gc740"
db_pass = "123456"
db_ip = "127.0.0.1"
error_log = "____"
# 定义日志输出格式
logging.basicConfig(level=logging.ERROR,
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                    datefmt='%Y-%m-%d %H:%M:%S',
                    filename=error_log,
                    filemode='a')


def writeDb(sql, data):
    """
    连接mysql数据库（写），并进行写的操作，如果连接失败，会把错误写入日志中，并返回false，如果sql执行失败，也会把错误写入日志中，并返回false，如果所有执行正常，则返回true
    """
    try:
        conn = psycopg2.connect(database=db_name, user=db_user, password=db_pass, host=db_ip, port=5432)
        cursor = conn.cursor()
        print '数据库连接成功'
    except Exception, e:
        print e
        logging.error('数据库连接失败:%s' % e)
        return False
    try:
        # sql = sql % data
        # print sql
        cursor.execute(sql, data)
        conn.commit()  # 提交事务
    except Exception, e:
        conn.rollback()  # 如果出错，则事务回滚
        logging.error('数据写入失败:%s' % e)
        return False
    finally:
        cursor.close()
        conn.close()
    print '数据库写数据成功'
    return True


def readDb(sql):
    """
    连接mysql数据库（从），并进行数据查询，如果连接失败，会把错误写入日志中，并返回false，如果sql执行失败，也会把错误写入日志中，并返回false，如果所有执行正常，则返回查询到的数据，这个数据是经过转换的，转成字典格式，方便模板调用，其中字典的key是数据表里的字段名
    """
    try:
        conn = psycopg2.connect(database=db_name, user=db_user, password=db_pass, host=db_ip, port=5432)
        cursor = conn.cursor()
    except Exception, e:
        print e
        logging.error('数据库连接失败:%s' % e)
        return False
    try:
        cursor.execute(sql)
        data = [dict((cursor.description[i][0], value) for i, value in enumerate(row)) for row in
                cursor.fetchall()]  # 转换数据，字典格式
    except Exception, e:
        logging.error('数据执行失败:%s' % e)
        return False
    finally:
        cursor.close()
        conn.close()
    return data


class Temporary(models.Model):
    _name = 'temporary.data'

    name = fields.Char()

    sale_order_id = fields.Integer("销售订单", readonly=True)
    partner = fields.Char("客户", readonly=True)
    saleman = fields.Char("销售员", readonly=True)
    crm_team = fields.Char("销售团队", readonly=True)

    # sale_order
    confirmation_date = fields.Datetime("确认日期", readonly=True)
    subscription_period = fields.Char("认购期限", readonly=True)
    amount_untaxed = fields.Float("未含税金额", readonly=True)
    amount_total = fields.Float("总计", readonly=True)
    invoice_status = fields.Char("发票状态", readonly=True)

    # product_template
    product_name = fields.Char("产品名", readonly=True)
    product_type = fields.Char("产品类型", readonly=True)
    list_price = fields.Float("标价", readonly=True)
    is_financial = fields.Boolean("金融产品", readonly=True)
    annual_interest = fields.Float("年利率", readonly=True)
    sale_bonus = fields.Float("产品提成", readonly=True)
    type_financial = fields.Char("金融产品类型", readonly=True)

    # sale_order_line
    product_uom_qty = fields.Float("订购数量", readonly=True)
    qty_invoiced = fields.Float("已开发票数量", readonly=True)
    price_unit = fields.Float("单价", readonly=True)
    product_description = fields.Char("产品说明", readonly=True)

    # account_invoice
    invoice_number = fields.Char("发票号码", readonly=True)
    date_invoice = fields.Date("开票日期", readonly=True)
    origin = fields.Char("源单据", readonly=True)
    date_due = fields.Date("截止日期", readonly=True)
    residual = fields.Float("截止金额", readonly=True)

    # 新增还款四个字段
    repayment_times_total = fields.Integer("还款总期数", readonly=True)
    repayment_time = fields.Integer("第几期还款", readonly=True)
    repayment_datetime = fields.Datetime("本期还款日期", readonly=True)
    repayment_amount = fields.Float("本期还款数额", readonly=True)
    repayment_status = fields.Boolean("还款状态", default=False, readonly=True)

    def cal(self):
        sql = "SELECT public.sale_order.id AS 销售订单, " \
              "public.sale_order.confirmation_date AS 确认日期, " \
              "public.sale_order.subscription_period AS 认购期限, " \
              "public.sale_order.amount_untaxed AS 未含税金额, " \
              "public.sale_order.amount_total AS 总计, " \
              "public.sale_order.invoice_status AS 发票状态, " \
              "public.res_partner.name AS 客户, " \
              "public.crm_team.name AS 销售团队, " \
              "public.product_template.name AS 产品名,	" \
              "public.product_template.list_price AS 标价,	" \
              "public.product_template.type AS 产品类型, " \
              "public.product_template.is_financial AS 金融产品, " \
              "public.product_template.annual_interest AS 年利率, " \
              "public.product_template.sale_bonus AS 产品提成, " \
              "public.product_template.type_financial AS 金融产品类型, " \
              "public.sale_order_line.product_uom_qty AS 订购数量, " \
              "public.sale_order_line.qty_invoiced AS 已开发票数量, " \
              "public.sale_order_line.price_unit AS 单价, " \
              "public.sale_order_line.name AS 产品说明, " \
              "public.account_invoice.number AS 发票号码, " \
              "public.account_invoice.date_invoice AS 开票日期, " \
              "public.account_invoice.origin AS 源单据, " \
              "public.account_invoice.date_due AS 截止日期, " \
              "public.account_invoice.residual AS 截止金额 " \
              "FROM public.sale_order " \
              "INNER JOIN public.sale_order_line ON public.sale_order_line.order_id = public.sale_order.id " \
              "INNER JOIN public.res_partner ON public.sale_order.partner_id = public.res_partner.id " \
              "INNER JOIN public.product_product ON public.sale_order_line.product_id = public.product_product.id " \
              "INNER JOIN public.product_template ON public.product_product.product_tmpl_id = public.product_template.id " \
              "INNER JOIN public.res_users ON public.sale_order.user_id = public.res_users.id " \
              "LEFT JOIN public.crm_team ON public.crm_team.id = public.res_users.sale_team_id " \
              "INNER JOIN public.account_invoice ON public.account_invoice.partner_id = public.res_partner.id " \
              "WHERE public.sale_order.invoice_status = 'invoiced'"
        data = readDb(sql)
        for i in range(len(data)):
            if int(data[i]["金融产品类型"]) == 2:  # 基金
                fund(data, i)

def cal():
    sql = "SELECT public.sale_order.id AS 销售订单, " \
          "public.sale_order.confirmation_date AS 确认日期, " \
          "public.sale_order.subscription_period AS 认购期限, " \
          "public.sale_order.amount_untaxed AS 未含税金额, " \
          "public.sale_order.amount_total AS 总计, " \
          "public.sale_order.invoice_status AS 发票状态, " \
          "public.res_partner.name AS 客户, " \
          "public.crm_team.name AS 销售团队, " \
          "public.product_template.name AS 产品名,	" \
          "public.product_template.list_price AS 标价,	" \
          "public.product_template.type AS 产品类型, " \
          "public.product_template.is_financial AS 金融产品, " \
          "public.product_template.annual_interest AS 年利率, " \
          "public.product_template.sale_bonus AS 产品提成, " \
          "public.product_template.type_financial AS 金融产品类型, " \
          "public.sale_order_line.product_uom_qty AS 订购数量, " \
          "public.sale_order_line.qty_invoiced AS 已开发票数量, " \
          "public.sale_order_line.price_unit AS 单价, " \
          "public.sale_order_line.name AS 产品说明, " \
          "public.account_invoice.number AS 发票号码, " \
          "public.account_invoice.date_invoice AS 开票日期, " \
          "public.account_invoice.origin AS 源单据, " \
          "public.account_invoice.date_due AS 截止日期, " \
          "public.account_invoice.residual AS 截止金额 " \
          "FROM public.sale_order " \
          "INNER JOIN public.sale_order_line ON public.sale_order_line.order_id = public.sale_order.id " \
          "INNER JOIN public.res_partner ON public.sale_order.partner_id = public.res_partner.id " \
          "INNER JOIN public.product_product ON public.sale_order_line.product_id = public.product_product.id " \
          "INNER JOIN public.product_template ON public.product_product.product_tmpl_id = public.product_template.id " \
          "INNER JOIN public.res_users ON public.sale_order.user_id = public.res_users.id " \
          "LEFT JOIN public.crm_team ON public.crm_team.id = public.res_users.sale_team_id " \
          "INNER JOIN public.account_invoice ON public.account_invoice.partner_id = public.res_partner.id " \
          "WHERE public.sale_order.invoice_status = 'invoiced'"
    data = readDb(sql)
    for i in range(len(data)):
        if int(data[i]["金融产品类型"]) == 2:  # 基金
            fund(data, i)
        # if int(data[i]["金融产品类型"]) == 1:  # 借款
        #     loan(data, i)

def fund(data, i):
    confirm_date = datetime.strptime(data[i]["确认日期"], "%Y-%m-%d %H:%M:%S")
    periods = int(data[i]["认购期限"]) / 3  # 还款次数
    interest = data[i]["总计"] * data[i]["年利率"] / 12 * 3  # 利息
    principal_interest = data[i]["总计"] + interest  # 本息
    first_repay_date = confirm_date + relativedelta(months=+3) + relativedelta(days=-1)  # 第一次还款日期
    for period in range(periods):
        repay_date = first_repay_date + relativedelta(months=+3) * period
        sql = "insert into public.temporary_data(sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, amount_total, invoice_status, product_name, product_type, list_price, is_financial, annual_interest, sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, product_description, invoice_number, date_invoice, origin, date_due, residual, repayment_times_total, repayment_time, repayment_datetime, repayment_amount)values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        if (period == (periods - 1)):
            data1 = (
                data[i]["销售订单"], data[i]["客户"], data[i]["销售团队"], data[i]["确认日期"], data[i]["认购期限"], data[i]["未含税金额"],
                data[i]["总计"], data[i]["发票状态"], data[i]["产品名"], data[i]["产品类型"], data[i]["标价"], data[i]["金融产品"],
                data[i]["年利率"], data[i]["产品提成"], data[i]["金融产品类型"], data[i]["订购数量"], data[i]["已开发票数量"],
                data[i]["单价"],
                data[i]["产品说明"], data[i]["发票号码"], data[i]["开票日期"], data[i]["源单据"], data[i]["截止日期"], data[i]["截止金额"],
                periods, periods, repay_date, principal_interest)
            writeDb(sql, data1)
        else:
            data1 = (
                data[i]["销售订单"], data[i]["客户"], data[i]["销售团队"], data[i]["确认日期"], data[i]["认购期限"], data[i]["未含税金额"],
                data[i]["总计"], data[i]["发票状态"], data[i]["产品名"], data[i]["产品类型"], data[i]["标价"], data[i]["金融产品"],
                data[i]["年利率"], data[i]["产品提成"], data[i]["金融产品类型"], data[i]["订购数量"], data[i]["已开发票数量"],
                data[i]["单价"],
                data[i]["产品说明"], data[i]["发票号码"], data[i]["开票日期"], data[i]["源单据"], data[i]["截止日期"], data[i]["截止金额"],
                periods, (period + 1), repay_date, interest)
            writeDb(sql, data1)


# def loan(data, i):
#     confirm_date = datetime.strptime(data[i]["确认日期"], "%Y-%m-%d %H:%M:%S")
#     periods = int(data[i]["认购期限"])  # 还款次数
#     first_interest = data[i]["总计"] * data[i]["年利率"] / 12 * 3  # 利息
#     interest = data[i]["总计"] * data[i]["年利率"] / 12 * 3  # 利息
#     principal_interest = data[i]["总计"] + interest  # 本息
#     if confirm_date.day > 10 & confirm_date.day <= 25:
#         first_repay_date = confirm_date.replace(day=25) + relativedelta(months=+1)  # 第一次还款日期
#     else:
#         first_repay_date = confirm_date.replace(day=10) + relativedelta(months=+1)  # 第一次还款日期
#     for period in range(periods):
#         repay_date = first_repay_date + relativedelta(months=+1) * period
#         sql = "insert into public.temporary_data(sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, amount_total, invoice_status, product_name, product_type, list_price, is_financial, annual_interest, sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, product_description, invoice_number, date_invoice, origin, date_due, residual, repayment_times_total, repayment_time, repayment_datetime, repayment_amount)values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
#         if (period == (periods - 1)):
#             data1 = (
#                 data[i]["销售订单"], data[i]["客户"], data[i]["销售团队"], data[i]["确认日期"], data[i]["认购期限"], data[i]["未含税金额"],
#                 data[i]["总计"], data[i]["发票状态"], data[i]["产品名"], data[i]["产品类型"], data[i]["标价"], data[i]["金融产品"],
#                 data[i]["年利率"], data[i]["产品提成"], data[i]["金融产品类型"], data[i]["订购数量"], data[i]["已开发票数量"], data[i]["单价"],
#                 data[i]["产品说明"], data[i]["发票号码"], data[i]["开票日期"], data[i]["源单据"], data[i]["截止日期"], data[i]["截止金额"],
#                 periods, periods, repay_date, principal_interest)
#             writeDb(sql, data1)
#         else:
#             data1 = (
#                 data[i]["销售订单"], data[i]["客户"], data[i]["销售团队"], data[i]["确认日期"], data[i]["认购期限"], data[i]["未含税金额"],
#                 data[i]["总计"], data[i]["发票状态"], data[i]["产品名"], data[i]["产品类型"], data[i]["标价"], data[i]["金融产品"],
#                 data[i]["年利率"], data[i]["产品提成"], data[i]["金融产品类型"], data[i]["订购数量"], data[i]["已开发票数量"], data[i]["单价"],
#                 data[i]["产品说明"], data[i]["发票号码"], data[i]["开票日期"], data[i]["源单据"], data[i]["截止日期"], data[i]["截止金额"],
#                 periods, (period + 1), repay_date, interest)
#             writeDb(sql, data1)


if __name__ == '__main__':
    cal()
